package FitNess;


import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JOptionPane;

import FitNess.ErreurLob;
import FitNess.FenetreConnexion;

/**
 * 
 * @author DJIGA SENE
 */
public final class TestConnectionBDMysql {

	static final String mydb = "fitness";
	static Connection maConnection = null;
	static Statement stm = null;
	static String chaine = "jdbc:mysql://localhost/" + mydb;
	static String login = "root";
	static String mdp = "";

	public static boolean SeConnecter() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			try {
				ErreurLob.RedigerErreur(e.getMessage());
			} catch (IOException e1) {
			}
		}
		try {
			maConnection = DriverManager.getConnection(chaine, login, mdp);
			stm = maConnection.createStatement();
			return true;
		} catch (SQLException sql) {
			JOptionPane
					.showMessageDialog(new FenetreConnexion(),
							"ERREUR DE CONNEXION A LA BASE DE DONNEE \n        RESSAYEZ PLUS TARD");
			System.exit(7);
		}
		return false;
	}

	public static boolean SeDeconnecter() {

		try {
			if (maConnection != null) {
				maConnection.close();
				if (stm != null)
					stm.close();
				return true;
			}
		} catch (SQLException sql) {
			try {
				ErreurLob.RedigerErreur(sql.getMessage());
			} catch (IOException e) {
			}
		}
		return false;
	}

	public static int ExecuteRequeteUpdate(String requete) {
		SeConnecter();
		try {
			if (stm != null) {
				return stm.executeUpdate(requete);
			}
		} catch (SQLException sql) {
			try {
				ErreurLob.RedigerErreur(sql.getMessage());
			} catch (IOException e) {
			}
		}
		SeDeconnecter();
		return -1;
	}

	public static boolean insertion(String sql) {

		SeConnecter();
		try {
			PreparedStatement ps = maConnection.prepareStatement(sql);
			ps.executeUpdate(sql);
			return true;
		} catch (SQLException sqlError) {
			try {
				ErreurLob.RedigerErreur(sqlError.getMessage());
			} catch (IOException e) {
			}
		}
		SeDeconnecter();
		return false;
	}

	public static ResultSet ExecuterRequeteSelection(String requete) {

		SeConnecter();
		try {
			if (stm != null)
				return stm.executeQuery(requete);
		} catch (SQLException sql) {
			try {
				ErreurLob.RedigerErreur(sql.getMessage());
			} catch (IOException e) {
			}
		}
		SeDeconnecter();
		return null;
	}

	public static boolean verifierExistenceCompte(String login, String pwd) {

		ResultSet rs = null;
		String req = "select mdp from compte where Login ='" + login + "'";
		try {
			rs = ExecuterRequeteSelection(req);
			String pwd1 = "";
			if (rs.next())
				pwd1 = rs.getString(1);
			if (pwd1.equals(pwd)) {
				return true;
			}
		} catch (SQLException ex) {
			try {
				ErreurLob.RedigerErreur(ex.getMessage());
			} catch (IOException e) {
			}
		}
		return false;
	}

	public static boolean enregistrerCompte(String login, String mdp,
			String privilege) {
		String sql = "INSERT INTO compte(login,mdp,privilege) VALUES ('"
				+ login + "','" + mdp + "','" + privilege + "')";
		boolean insert = insertion(sql);
		return insert;
	}

	public static boolean enregistrerMoniteur(String nomMoni,
			String prenomMoni, String mailMoni, String numTelMoni,
			String adresseMoni, int idcom) {
		String sql = "INSERT INTO moniteur(nomMoni,prenomMoni,mailMoni,numTelMoni,adresseMoni,idCompte) VALUES ('"
				+ nomMoni
				+ "','"
				+ prenomMoni
				+ "','"
				+ mailMoni
				+ "','"
				+ numTelMoni + "','" + adresseMoni + "','" + idcom + "')";
		boolean insert = insertion(sql);
		return insert;
	}

	public static boolean enregistrerConseil(String niveauCons,
			String defConseil, String plageDAge) {
		String sql = "INSERT INTO conseil (niveauCons,plageDAge,defconseil,idMoniteur) VALUES ('"
				+ niveauCons
				+ "','"
				+ plageDAge

				+ "','"
				+ defConseil
				+ "','"
				+ FenetreConnexion.getIdCompte()
				+ "')";
		boolean insert = insertion(sql);
		return insert;
	}

	public static boolean enregistrerClient(String nomCli, String prenomCli,
			String mailCli, String numTelCli, String adresseCli, String ageCli,
			String genreCli, String niveauCli) {
		String sql = "INSERT INTO client (nomCli,prenomCli,mailCli,numTelCli,adresseCli,ageCli,genreCli,niveauCli) VALUES ('"
				+ nomCli
				+ "','"
				+ prenomCli
				+ "','"
				+ mailCli
				+ "','"
				+ numTelCli
				+ "','"
				+ adresseCli
				+ "','"
				+ ageCli
				+ "','"
				+ genreCli + "','" + niveauCli + "')";
		boolean insert = insertion(sql);
		return insert;
	}

	public static boolean enregistrerGerant(String nomGer, String prenomGer,
			String mail, String numTel, String adresse, int idCompte) {
		String sql = "INSERT INTO gerant(nomGer,prenomGer,mail,numTel,adresse,idCompte) VALUES ('"
				+ nomGer
				+ "','"
				+ prenomGer
				+ "','"
				+ mail
				+ "','"
				+ numTel
				+ "','" + adresse + "','" + idCompte + "')";
		boolean insert = insertion(sql);
		return insert;
	}

	public static boolean Supprimer(int id, String table) {
		String req;
		String req1;
		if (table.equals("moniteur")) {
			req = " delete from Moniteur where idMoniteur='" + id + "'";
			TestConnectionBDMysql
					.ExecuteRequeteUpdate("update Conseil set idConseil='"
							+ 10008 + "' where idMoniteur='" + id + "'");
			TestConnectionBDMysql
					.ExecuteRequeteUpdate("delete from Organiser where idMoniteur='"
							+ id + "'");
		} else
			req = " delete from Gerant where idGerant='" + id + "'";
		req1 = "select idCompte From " + table + " where id" + table + " = '"
				+ id + "'";
		ResultSet rs = TestConnectionBDMysql.ExecuterRequeteSelection(req1);
		try {
			rs.next();
			int idCom = rs.getInt(1);
			System.out.println(idCom);
			int n = TestConnectionBDMysql.ExecuteRequeteUpdate(req);
			if (n == 1) {
				System.out.println(n);
				int m = TestConnectionBDMysql
						.ExecuteRequeteUpdate("delete from  Compte where idCompte ='"
								+ idCom + "'");
				if (m == 1) {
					return true;
				}else 
					return false;
			}else 
				System.out.println(n);
				return false;
		} catch (SQLException e) {
			try {
				ErreurLob.RedigerErreur(e.getMessage());
			} catch (IOException e1) {
			}
		}
			return false;
	}
}